-- @owner: Alice
-- @date: 2025-09-03
-- @testpoint: 确定性函数支持result_cache,plpgsql函数，合理报错
@conn PrimaryDbAmode;
drop extension if exists gms_output;
drop function if exists get_film_count;
drop function if exists batch_insert_proc;
drop function if exists exception_proc;
drop function if exists f_get_employee;
drop function if exists f_dept_salary;
drop function if exists f_get_rows;
drop function if exists f_dept_salary_out2;
drop function if exists getsum;
drop function if exists func_sum;
drop function if exists func_sum_3;
drop table if exists all_support_type;
drop table if exists department cascade;
drop table if exists employee cascade;
drop table if exists film;
drop table if exists mytab;
drop table if exists tmp;
drop table if exists tt_cache;
drop table if exists t_user;
drop type if exists dept_salary;
create extension gms_output;
--
set enable_func_cache = 1;
select gms_output.enable();
set query_dop=1;


set behavior_compat_options='skip_insert_gs_source,select_into_return_null,proc_outparam_override';
--创建表
drop table if exists tt_cache;
create table tt_cache(
id int,
col_smallint smallint
);
insert into tt_cache values(
generate_series(1,20)%4,
generate_series(1,20)%3
);
select count(*) from tt_cache;
drop table if exists film;
create table film(id int,length int);
insert into film values( 1 ,1);
insert into film values( 2 ,2);
insert into film values( 3 ,0);
insert into film values( 4 ,2);
insert into film values( 5 ,0);
insert into film values( 6 ,1);
insert into film values( 7 ,1);
insert into film values( 8 ,0);
insert into film values( 9 ,1);
insert into film values(10 ,2);
select * from film;
--case1
create or replace function get_film_count(len_from int, len_to int)
returns int stable result_cache
language plpgsql
/
as
$$
declare
film_count integer;
begin
raise notice 'NOTICE: excute function...';
select count(*)
into film_count
from film
where length between len_from and len_to;
return film_count;
end;
$$;
select get_film_count(100,12);
select get_film_count(id,col_smallint) from tt_cache;
select get_film_count(get_film_count(id,col_smallint),col_smallint) from tt_cache;
select get_film_count(12,100) from tt_cache;
--case2
drop table if exists t_user;
create table t_user (
id serial,
user_name varchar(255),
pass_word varchar(255),
create_time date,
dr char(1),
primary key(id)
);
create or replace function batch_insert_proc(num int) returns void result_cache as $$
declare
randNum int;
begin
raise notice 'NOTICE: excute function...';
while num > 0 loop
randNum := round(random()*num);
insert into t_user(user_name,pass_word,create_time,dr) values('user_name'||randNum,'pass_word'||randNum,now(),'0');
num = num - 1;
end loop;
exception
when others then
raise exception'(%)', 'unkown err';
end;
$$ language plpgsql;
/
select batch_insert_proc(2);
select batch_insert_proc(id) from tt_cache;
select batch_insert_proc(batch_insert_proc(col_smallint)) from tt_cache;
select batch_insert_proc(3) from tt_cache;
select count(*) from t_user;
--case3
drop table if exists mytab;
create table mytab(
first_name varchar(255),
last_name varchar(255)
);
INSERT INTO mytab(first_name, last_name) VALUES('Lei', 'DaGou');
select * from mytab;
-- 4.创建存储函数
create or replace function exception_proc() returns int as $$
declare
x int default 0;
y int default 0;
BEGIN
raise notice 'NOTICE: excute function...';
UPDATE mytab SET first_name = 'Lei' WHERE last_name = 'ErGOU';
x := x + 1;
y := x / 0;
return x;
EXCEPTION
WHEN division_by_zero THEN
raise notice 'NOTICE: caught division_by_zero(%)',x;
RETURN x;
END;
$$ language plpgsql volatile result_cache;
/
select exception_proc();
select exception_proc() from tt_cache;
--case4
drop table if exists department cascade;
drop table if exists employee cascade;
create table department(id int primary key, name text);
create table employee(id int primary key, name text, salary int, departmentid int references department);
insert into department values (1, 'Management'),(2, 'IT'),(3, 'BOSS');
insert into employee values (1, 'kenyon', 30000, 1);
insert into employee values (2, 'francs', 50000, 1);
insert into employee values (3, 'digoal', 60000, 2);
insert into employee values (4, 'narutu', 120000, 3);
create or replace function f_get_employee()
returns setof employee
as
$$
begin
raise notice 'NOTICE: excute function...';
return query select * from employee;
end;
$$
language plpgsql result_cache stable;
/
select * from f_get_employee();
select f_get_employee();
select * from f_get_employee() where id >3;
select f_get_employee() from tt_cache;
create or replace type dept_salary as object(departmentid int, totalsalary int);
create or replace function f_dept_salary()
returns setof dept_salary
as
$$
declare
rec dept_salary%rowtype;
begin
raise notice 'NOTICE: excute function...';
for rec in select departmentid, sum(salary) as totalsalary from f_get_employee() group by departmentid loop
return next rec;
end loop;
return;
end;
$$
language 'plpgsql' stable result_cache;
/
select * from f_dept_salary();
create or replace function f_get_rows(text) returns setof record as
$$
declare
rec record;
begin
raise notice 'NOTICE: excute function...';
for rec in EXECUTE 'select * from ' || $1 loop
return next rec;
end loop;
return;
end
$$
language 'plpgsql' result_cache immutable;
/
select * from f_get_rows('department') as dept(deptid int, deptname text);
select * from f_get_rows('employee') as employee(employee_id int, employee_name text,employee_salary int,dept_id int);
create or replace function f_dept_salary_out2(int, out o_dept text,out o_salary text)
returns setof record as
$$
declare
v_rec record;
begin
raise notice 'NOTICE: excute function...';
for v_rec in EXECUTE 'select departmentid as dept_id, sum(salary) as total_salary from f_get_employee() group by departmentid having departmentid='||$1 loop
o_dept:=v_rec.dept_id;
o_salary:=v_rec.total_salary;
return next;
end loop;
end;
$$
language plpgsql result_cache;
/
select * from f_dept_salary_out2(2);
--case4
drop table if exists tmp;
create table tmp(
bsm VARCHAR(100),
name VARCHAR(100),
num int
);
insert into tmp(bsm,name,num) VALUES('a','苹果',21);
insert into tmp(bsm,name,num) VALUES('b','香蕉',11);
create or replace function getsum(in talename VARCHAR)
RETURNS int as $$
DECLARE
stmt VARCHAR;
count int;
begin
raise notice 'NOTICE: excute function...';
stmt:=format('select count(1) from %s', talename);
raise notice 'NOTICE: %',stmt;
EXECUTE stmt into count;
return count;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
end; $$ LANGUAGE plpgsql result_cache immutable;
/
select getsum('tmp');
select getsum(id::varchar(40)) from tt_cache;
select getsum(getsum(col_smallint::varchar(30))::varchar(40)) from tt_cache;
select getsum('12') from tt_cache;
select getsum(12) from tt_cache;

--case5
drop table if exists all_support_type;
create table all_support_type(
id int,
col1 int,
col2 int,
col3 int
);
insert into all_support_type values(
generate_series(1,20),
generate_series(1,20)%2,
generate_series(1,20)%2,
generate_series(1,20)%3
);
select count(*) from all_support_type;

create or replace function func_sum(a int,b int)
returns int
as $$
declare
sum int;
begin
raise notice 'NOTICE: excute function func_sum...';
return a + b;
end;
$$language plpgsql stable result_cache;
/


create or replace function func_sum_3(a int,b int,c int)
returns int
as $$
declare
sum int;
begin
raise notice 'NOTICE: excute function func_sum_3...';
sum = func_sum(a,b);
return sum + c;
end;
$$language plpgsql stable result_cache;
/

select func_sum_3(1,2,3);
select func_sum_3(1,2,3) from all_support_type;
select func_sum_3(col1,col2,col3) from all_support_type;
select func_sum_3(col1,func_sum_3(col1,col2,col3),func_sum_3(col1,col2,col3)) from all_support_type;
--teardown
drop function if exists get_film_count;
drop function if exists batch_insert_proc;
drop function if exists exception_proc;
drop function if exists f_get_employee;
drop function if exists f_dept_salary;
drop function if exists f_get_rows;
drop function if exists f_dept_salary_out2;
drop function if exists getsum;
drop function if exists func_sum;
drop function if exists func_sum_3;
drop table if exists all_support_type;
drop table if exists department cascade;
drop table if exists employee cascade;
drop table if exists film;
drop table if exists mytab;
drop table if exists tmp;
drop table if exists tt_cache;
drop table if exists t_user;
drop type if exists dept_salary;
